package crm.control;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import crm.dbcoon.DBConnect;
import crm.model.Employees;
import crm.model.Schedule;
import crm.util.Pagination;
  
public class ScheduleControl {
	public static Pagination pageInstance = null;

	public static int getScheduleId(int employeesId){
		DBConnect dbc = new DBConnect();
		String sql = "select * from common_schedule where creater = ?";
		dbc.prepareStatement(sql);
		try {
			dbc.setInt(1, employeesId);
			ResultSet rs = dbc.executeQuery();
			if(rs.next()){
				return rs.getInt("id");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return 0;
		} finally{
			dbc.close();
		}
		return 0;
	}
	public static int getScheduleId(Employees employees){
		DBConnect dbc = new DBConnect();
		String sql = "select * from common_schedule where creater = ?";
		dbc.prepareStatement(sql);
		try {
			dbc.setInt(1, employees.getId());
			ResultSet rs = dbc.executeQuery();
			if(rs.next()){
				return rs.getInt("id");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return 0;
		} finally{
			dbc.close();
		}
		return 0;
	}
	
	public static int getScheduleId(String employeesName){
		DBConnect dbc = new DBConnect();
		String sql = "select * from common_schedule where creater = ?";
		dbc.prepareStatement(sql);
		try {
			dbc.setInt(1, EmployeesControl.getEmployeesID(employeesName));
			ResultSet rs = dbc.executeQuery();
			if(rs.next()){
				return rs.getInt("id");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return 0;
		} finally{
			dbc.close();
		}
		return 0;
	}
	public static Schedule getSecheduleInfomation(int id){
		DBConnect dbc = new DBConnect();
		String sql = "select * from common_schedule where id = ?";
		dbc.prepareStatement(sql);
		try {
			dbc.setInt(1, id);
			Schedule schedule = new Schedule();
			ResultSet rs =  dbc.executeQuery();
			if(rs.next()){
				schedule.setContent(rs.getString("content"));
				schedule.setCreate_time(rs.getString("create_time"));
				schedule.setCreater(rs.getInt("id"));
				schedule.setFinish_time(rs.getString("finish_time"));
				schedule.setRelated_customer(rs.getString("related_customer"));
				schedule.setRelated_receipt(rs.getString("related_receipt"));
				schedule.setResult(rs.getString("result"));
				schedule.setState(rs.getString("state"));
				schedule.setTheme(rs.getString("theme"));
				return schedule;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally{
			dbc.close();
		}
		return null;
	}
	public static Schedule getSecheduleInfomation(Employees employees){
		DBConnect dbc = new DBConnect();
		String sql = "select * from common_schedule where creater = ?";
		dbc.prepareStatement(sql);
		try {
			dbc.setInt(1, employees.getId());
			Schedule schedule = new Schedule();
			ResultSet rs =  dbc.executeQuery();
			if(rs.next()){
				schedule.setContent(rs.getString("content"));
				schedule.setCreate_time(rs.getString("create_time"));
				schedule.setCreater(rs.getInt("id"));
				schedule.setFinish_time(rs.getString("finish_time"));
				schedule.setRelated_customer(rs.getString("related_customer"));
				schedule.setRelated_receipt(rs.getString("related_receipt"));
				schedule.setResult(rs.getString("result"));
				schedule.setState(rs.getString("state"));
				schedule.setTheme(rs.getString("theme"));
				return schedule;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally{
			dbc.close();
		}
		return null;
	}
	
	public static Schedule getSecheduleInfomation(String employeesName){
		DBConnect dbc = new DBConnect();
		String sql = "select * from common_schedule where creater = ?";
		dbc.prepareStatement(sql);
		try {
			Employees e = EmployeesControl.getEmpolyeesById(EmployeesControl.getEmployeesID(employeesName));
			dbc.setInt(1,e.getId());
			Schedule schedule = new Schedule();
			ResultSet rs =  dbc.executeQuery();
			if(rs.next()){
				getByRs(schedule, rs);
				return schedule;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally{
			dbc.close();
		}
		return null;
	}
	
	public static boolean addSchedule(Schedule schedule){
		DBConnect dbc=new DBConnect();
		String sql = "insert into common_schedule (creater,create_time,theme,content,state,finish_time,related_customer)" +
				" values (?,?,?,?,?,?,?)";
		dbc.prepareStatement(sql);
		try
		{	
			dbc.setInt(1, schedule.getCreater());
			dbc.setString(2, schedule.getCreate_time());
			dbc.setString(3, schedule.getTheme());
			dbc.setString(4, schedule.getContent());
			dbc.setString(5, schedule.getState());
			dbc.setString(6, schedule.getFinish_time());
			dbc.setString(7, schedule.getRelated_customer());
			dbc.executeUpdate();
			return true;
		}catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			dbc.close();
		}
	}
	
	public static boolean deleteSchedule(int id){
		DBConnect dbc = new DBConnect();
		String sql = "DELETE FROM common_schedule WHERE id = " + id;
		dbc.prepareStatement(sql);
		try {
			dbc.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally{
			dbc.close();
		}
	}
	
	public static boolean updateSchedule(Schedule schedule){
		DBConnect dbc = new DBConnect();
		String sql = "update common_schedule set creater = ?, create_time = ?, theme = ?, content = ?, state = ?, result = ?, finish_time = ?, related_customer = ?, related_receipt = ? where id = ?";
		dbc.prepareStatement(sql);
		try {
			dbc.setInt(1, schedule.getCreater());
			dbc.setString(2, schedule.getCreate_time());
			dbc.setString(3, schedule.getTheme());
			dbc.setString(4, schedule.getContent());
			dbc.setString(5, schedule.getState());
			dbc.setString(6, schedule.getResult());
			dbc.setString(7, schedule.getFinish_time());
			dbc.setString(8, schedule.getRelated_customer());
			dbc.setString(9, schedule.getRelated_receipt());
			dbc.setInt(10, schedule.getId());
			dbc.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally{
			dbc.close();
		}
	}
	public static void getByRs(Schedule schedule, ResultSet rs) {
		// TODO Auto-generated method stub
		try {
			schedule.setContent(rs.getString("content"));
			schedule.setCreate_time(rs.getString("create_time"));
			schedule.setCreater(rs.getInt("creater"));
			schedule.setFinish_time(rs.getString("finish_time"));
			schedule.setRelated_customer(rs.getString("related_customer"));
			schedule.setRelated_receipt(rs.getString("related_receipt"));
			schedule.setResult(rs.getString("result"));
			schedule.setState(rs.getString("state"));
			schedule.setTheme(rs.getString("theme"));
			schedule.setId(rs.getInt("id"));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("getByRs error!");
		}
	}
	
	public static boolean finishSchedule(Schedule schedule,int id){
		DBConnect dbConnect = new DBConnect();
		String sqlString = "update common_schedule set finish_time=?,state=? where id="+id;
		dbConnect.prepareStatement(sqlString);
		try{
			dbConnect.setString(1, schedule.getFinish_time());
			dbConnect.setString(2, schedule.getState());
			dbConnect.executeUpdate();
			return true;
		}catch(SQLException e){
			e.printStackTrace();
			System.out.print(sqlString);
			return false;
		}finally{
			dbConnect.close();
		}
	}
	
	public Collection getSchedule(int pages,int rowsPerpage,String create_time)
	throws SQLException,Exception{
		String sqlString = "select * from common_schedule order by '"+create_time+"' desc";
		pageInstance = new ScheduleControlPage(sqlString, rowsPerpage);
		return pageInstance.getPage(pages);
	}
	/*public static void main(String args[]){
//		Employees e = new Employees();
//		e.setId(3);
//		Schedule s = getSecheduleInfomation(e);
//		if(s!=null){
//		System.out.println(s.getContent());
//		System.out.println(s.getCreate_time());
//		System.out.println(s.getCreater());
//		System.out.println(s.getFinish_time());
//		System.out.println(s.getRelated_customer());
//		System.out.println(s.getRelated_receipt());
//		System.out.println(s.getResult());
//		System.out.println(s.getTheme());
//		System.out.println(s.getState());
//		}else System.out.println("not exist!");
//		System.out.println("\n\n");
//		System.out.println(getScheduleId("lihang"));
		
		
		
//		Schedule s = new Schedule();
//		s.setContent("duj");
//		s.setCreate_time("jinan");
//		s.setCreater(3);
//		s.setFinish_time("zuian");
//		s.setRelated_customer("buidao");
//		s.setRelated_receipt("yebidao");
//		s.setResult("e");
//		s.setState("men");
//		s.setTheme("duanshutouzhu");
//		if(addSchedule(s)){
//			System.out.println("Yes");
//		}else System.out.println("no");
//		
//		
//		if(deleteSchedule(23)){
//			System.out.println("Yes");
//		}else System.out.println("no");
		
//		Schedule s = new Schedule();
//		s.setId(1);
//		s.setContent("duj");
//		s.setCreate_time("jinan");
//		s.setCreater(3);
//		s.setFinish_time("zuian");
//		s.setRelated_customer("buidao");
//		s.setRelated_receipt("yebidao");
//		s.setResult("e");
//		s.setState("men");
//		s.setTheme("duanshutouzhu");
//		if(updateSchedule(s)){
//			System.out.println("Yes");
//		}else System.out.println("no");
	}*/
}

class ScheduleControlPage extends Pagination{
	public ScheduleControlPage(String sql,int rowsPerPage){
		super(sql, rowsPerPage, 1);
	}
	protected Collection<Schedule> packResultSet(ResultSet rs){
		Collection<Schedule> slist= new ArrayList<Schedule>();
		try{
		while(rs.next()){
			Schedule schedule = new Schedule();
			ScheduleControl.getByRs(schedule, rs);
			slist.add(schedule);
			schedule = null;
		}
		}catch (Exception e) {
			e.printStackTrace();
		}
		return slist;
	}
}
